"""
@File    : mysql_operate.py
@Time    : 2021/5/30 23:34
@Author  : chenhuayan
@Software: PyCharm
"""
# !/usr/bin/env python3
# -*- coding: utf-8 -*-
import pymysql
from config.setting import MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB


class MysqlDb():
    def __init__(self, host, port, user, passwd, db):
        # 建立数据库连接
        self.coon = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db)
        # 通过cursor() 创建游标对象，并让查询结果以字典格式输出
        self.cur = self.coon.cursor(cursor=pymysql.cursors.DictCursor)

    def select_db(self, sql):
        """查询"""
        # 检查连接是否端口，如果断开则进行重连
        self.coon.ping(reconnect=True)
        # 使用execute() 执行sql
        self.cur.execute(sql)
        # 使用fetchall() 获取查询结果
        data = self.cur.fetchall()
        return data

    def execute_db(self, sql):
        """更新/新增/删除"""
        try:
            self.coon.ping(reconnect=True)
            num = self.cur.execute(sql)
            self.coon.commit()
            return num
        except Exception as e:
            print(f'操作出现错误：{e}')
            self.coon.rollback()


db = MysqlDb(MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWD, MYSQL_DB)
